﻿#region NameSpace
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
#endregion

namespace eOffice
{
   
   public class Procedure
   {
       #region Fields
       SqlCommand command;
       SqlConnection connection;
       DataSet dataSet;
       SqlDataAdapter sqlDataAdapter;       
       #endregion

       #region Constructor
       /// <summary>
       /// Connection to database
       /// </summary>
       public Procedure()
       {
          // string conString = "";
           connection = new SqlConnection("Context Connection=true");
       }

       #endregion

       #region Methods
       /// <summary>
       /// returns number of rows affected (Use Insert/Update/Delete statement)
       /// </summary>
       /// <returns></returns>
       public int ExecuteNonQuery(string query)
       {

           int count = 0;
           command = new SqlCommand("EXEC sp_configure 'clr enabled', 1 RECONFIGURE", connection);  
           //command = new SqlCommand();
           //command.Connection = connection;
           command.CommandType = CommandType.Text;
           command.CommandText = query;
           if(connection.State==ConnectionState.Closed)
           connection.Open();
           count = command.ExecuteNonQuery();
           connection.Close();
           return count;
       }

       /// <summary>
       /// returns DataSet(Use Select Statement)
       /// </summary>
       /// <param name="query"></param>
       /// <returns></returns>
       public DataSet ExecuteQuery(string query)
       {
           command = new SqlCommand("EXEC sp_configure 'clr enabled', 1 RECONFIGURE", connection);  
           //command = new SqlCommand();
           dataSet = new DataSet();
           //command.Connection = connection;
           command.CommandText = query;
           if (connection.State == ConnectionState.Closed)
           connection.Open();
           sqlDataAdapter = new SqlDataAdapter(command);
           sqlDataAdapter.Fill(dataSet);
           connection.Close();
           return dataSet;
       }
       /// <summary>
       /// returs single value(1st Column of 1st row)
       /// </summary>
       /// <param name="query"></param>
       /// <returns></returns>
       public object ExecuteScalar(string query)
       {
           Object count;
           command = new SqlCommand("EXEC sp_configure 'clr enabled', 1 RECONFIGURE", connection);  
          // command = new SqlCommand();
           //command.Connection = connection;
           command.CommandType = CommandType.Text;
           command.CommandText = query;
           if (connection.State == ConnectionState.Closed)
           connection.Open();
           count =command.ExecuteScalar();
           connection.Close();
           return count;
       }
       #endregion
   }
}



